package com.tnl.app.dao;

import java.util.List;

import com.tnl.app.vo.JobRequestVo;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import com.tnl.app.entity.JobDesc;
import com.tnl.app.entity.PlatformEnum;
import com.tnl.app.entity.Recommend;
import com.tnl.base.BaseDAO;

@Repository
public class JobDAO {
	public static final int PAGE_SIZE = 8;
	private static Logger logger = LoggerFactory.getLogger(JobDAO.class);
	
	final String SQL_GET_JOBDESC = "SELECT * from tb_jobdesc where platformid != 2 and status=1 ";
	final String SQL_GET_JOBDESC_COUNT="SELECT * from tb_jobdesc where platformid != 2 and status=1";
	private static final  String SQL_LIST_JOBDESC_BY_PARAM = "select job.* from tb_jobdesc as job left JOIN tb_jobtitle as title on job.expectJobId = title.id\n" +
			"left JOIN tb_jobtitle_group as titleGroup on titleGroup.id = title.postype where job.status = 1 and job.platformid != 2 ";
	
	public int getTotalNumForJobByHot(JobRequestVo jobRequestVo){
		StringBuilder sb = new StringBuilder(SQL_GET_JOBDESC);
		if (!StringUtils.isBlank(jobRequestVo.getKeyWord())) {
			sb.append(" and name like '%").append(jobRequestVo.getKeyWord()).append("%'");

		}
		if (jobRequestVo.getCompanyIds() != null){
			sb.append("or companyid in (");
			for(Integer s : jobRequestVo.getCompanyIds()){
				sb.append(s).append(",");
			}
			sb.deleteCharAt(sb.length()-1);
			sb.append(")");
		}
		if(jobRequestVo.getDomain() != null && jobRequestVo.getDomain().intValue() != 0){
			sb.append(" and domainid=").append(jobRequestVo.getDomain());
		}
		if (jobRequestVo.getSalary() != null && jobRequestVo.getDomain().intValue() != 0){
			Integer minSalary = 0;
			Integer maxSalary = 99999999;
			switch (jobRequestVo.getDomain().intValue()){
				case 1:
					minSalary = 10;
					maxSalary = 20;
					break;
				case 2:
					minSalary = 20;
					maxSalary = 30;
					break;
				case 3:
					minSalary = 30;
					maxSalary = 50;
					break;
				case 4:
					minSalary = 50;
					maxSalary = 100;
					break;
				case 5:
					minSalary = 100;
					break;
			}
			sb.append(" and annualPayment between ").append(minSalary).append(" and ").append(maxSalary);
		}
		if (jobRequestVo.getCity() != null && jobRequestVo.getCity().intValue() != 0){
			sb.append(" and cityid=").append(jobRequestVo.getCity());
		}
		List<JobDesc> list = BaseDAO.queryForList(JobDesc.class,sb.toString());
		if(list==null){
			return 0;
		}
		return list.size();
	}
	
	public List<JobDesc> getJobByHotList(JobRequestVo jobRequestVo, int offset, int pageSize) {
		StringBuilder sb = new StringBuilder(SQL_GET_JOBDESC);
		if (!StringUtils.isBlank(jobRequestVo.getKeyWord())) {
			sb.append(" and (name like '%").append(jobRequestVo.getKeyWord()).append("%'");
			if ( jobRequestVo.getCompanyIds() != null){
				sb.append("or companyid in (");
				for(Integer s : jobRequestVo.getCompanyIds()){
					sb.append(s).append(",");
				}
				sb.deleteCharAt(sb.length()-1);
				sb.append(")");
			}
			sb.append(")");
		}

		if(jobRequestVo.getDomain() != null && jobRequestVo.getDomain().intValue() != 0){
			sb.append(" and domainid=").append(jobRequestVo.getDomain());
		}
		if (jobRequestVo.getSalary() != null && jobRequestVo.getSalary().intValue() != 0){
			Integer minSalary = 0;
			Integer maxSalary = 99999999;
			switch (jobRequestVo.getSalary().intValue()){
				case 1:
					minSalary = 10;
					maxSalary = 20;
					break;
				case 2:
					minSalary = 20;
					maxSalary = 30;
					break;
				case 3:
					minSalary = 30;
					maxSalary = 50;
					break;
				case 4:
					minSalary = 50;
					maxSalary = 100;
					break;
				case 5:
					minSalary = 100;
					break;
			}
			sb.append(" and annualPayment between ").append(minSalary).append(" and ").append(maxSalary);
		}
		if (jobRequestVo.getCity() != null && jobRequestVo.getCity().intValue() != 0){
			sb.append(" and cityid=").append(jobRequestVo.getCity());
		}
		sb.append(" order by updateDate desc,id desc limit ?,?");
		System.out.println("SQL: "+ sb.toString());
		List<JobDesc> list = BaseDAO.queryForList(JobDesc.class,sb.toString(),offset,pageSize);
		return list;
	}
	
	final String SQL_GET_SIMILARPOSI = "SELECT * from tb_jobdesc where 1=1 and status=1 and name like ? order by id desc limit ?,?";
	final String SQL_GET_SIMILARPOSI_COUNT="SELECT * from tb_jobdesc where 1=1 and status=1 and name like ? ";
	public int getTotalNumForsimilarPosi(String name){
		List<JobDesc> list = BaseDAO.queryForList(JobDesc.class,SQL_GET_SIMILARPOSI_COUNT);
		if(list==null){
			return 0;
		}
		return list.size();
	}
	public List<JobDesc> similarPosi(int offset, int pageSize,String name) {
		List<JobDesc> list = BaseDAO.queryForList(JobDesc.class,SQL_GET_SIMILARPOSI,offset,pageSize,name);
		return list;
	}
	
	/**
	 * 公司分类（top500强/行业新贵）
	 */
	final String SQL_GET_COMPANYTAGSPOSI = "select a.* from tb_jobdesc a LEFT JOIN tb_company b on a.companyid=b.id where a.status=1 and a.platformid != 2 and b.tags=? ";
	final String SQL_GET_COMPANYTAGSPOSI_COUNT="select count(a.id) from tb_jobdesc a LEFT JOIN tb_company b on a.companyid=b.id where a.status=1 and a.platformid != 2 and b.tags=? ";
	public int getTotalNumForcompanyTagsPosi(String param, String tags){
		String sql = SQL_GET_COMPANYTAGSPOSI_COUNT;
		if (!StringUtils.isBlank(param)) {
			sql += " and (a.`name` like '%"+param+"%' or b.`name` like '%"+param+"%' or a.cityid = '"+param+"')";
		}
		Long count = BaseDAO.getCountBySql(sql, tags);


		return count.intValue();
	}
	public List<JobDesc> companyTagsPosi(int offset, int pageSize, String param, String tags) {
		List<JobDesc> list = null;
		String sql = SQL_GET_COMPANYTAGSPOSI;
		if (StringUtils.isBlank(param)) {
			sql += " order by a.updateDate DESC LIMIT ?,?";
		} else {
			sql += " and (a.`name` like '%"+param+"%' or b.`name` like '%"+param+"%' or a.cityid = '"+param+"') order by a.updateDate DESC LIMIT ?,?";
		}
		list = BaseDAO.queryForList(JobDesc.class,sql,tags,offset, pageSize);

		return list; 
	}
	
	/**
	 *超高悬赏
	 */
	final String SQL_GET_HIGHSALAYPOSI_COUNT="select count(*) from tb_jobdesc where status = 1 and platformid !=2 and reward>? LIMIT 0,1";
	final String SQL_GET_HIGHSALAYPOSI="select * from tb_jobdesc where status = 1 and platformid !=2 and reward>? order by reward DESC LIMIT ?,?";
	public int getTotalNumForHighSalayPosi(String param, Integer reward){
		String sql = SQL_GET_HIGHSALAYPOSI_COUNT;
		if (StringUtils.isNotBlank(param)) {
			sql = " select count(1) from tb_jobdesc j left join tb_company c on j.companyid = c.id where j.status = 1 and platformid !=2 and "
					+ " reward > ? and (j.name like '%"+param+"%' or c.name like '%"+param+"%' or j.cityid = '"+param+"')";
		}
		System.out.println("SQL: "+sql);
		List<JobDesc> list=BaseDAO.queryForList(JobDesc.class, sql,reward);
		if(list==null) {
			return 0;
		}
		return list.size();
	}
	
	public List<JobDesc> highSalayPosi(int offset,int pageSize,String param,Integer reward){
		String sql = SQL_GET_HIGHSALAYPOSI;
		if (StringUtils.isNotBlank(param)) {
			sql = " select j.* from tb_jobdesc j left join tb_company c on j.companyid = c.id where j.status = 1 and "
					+ " reward > ? and (j.name like '%"+param+"%' or c.name like '%"+param+"%' or j.cityid = '"+param+"')"
							+ " order by reward DESC LIMIT ?,?";
		
		}
		System.out.println("SQL: "+sql);
		List<JobDesc> list=BaseDAO.queryForList(JobDesc.class, sql,reward,offset,pageSize);
		return list;
	}
	
	/**
	 * index search position
	 */
	
	public int getTotalNumForSearchPosi(String searchName){
		final String SQL_GET_SEARCHPOSI_COUNT="select count(*) from tb_jobdesc where status = 1 and name like '%"+searchName+"%' LIMIT 0,1";
		List<JobDesc> list=BaseDAO.queryForList(JobDesc.class, SQL_GET_SEARCHPOSI_COUNT);
		if(list==null) {
			return 0;
		}
		return list.size();
	}
	
	public List<JobDesc> searchPosi(int offset,int pageSize,String searchName){
		String SQL_GET_SEARCHPOSI="select * from tb_jobdesc where status = 1 and name like '%"+searchName+"%' order by updateDate DESC LIMIT ?,?";
		List<JobDesc> list=BaseDAO.queryForList(JobDesc.class, SQL_GET_SEARCHPOSI,offset,pageSize);
		return list;
	}
	
	/**
	 * 相似职位 第一个参数是emp的现任职位，
	 * expectJob is emp expectJob
	 * roleId
	 * expectJobId is jobdesc expectJobId
	 */
	final String SQL_GET_SIMILARPOSI2 = "SELECT * from tb_jobdesc  where platformid !=2 and roleid in(?,?,?,?) and ? < expireddate and id<>? order by id desc limit 1,5";
	public List<JobDesc> similarPosi2(long roleId,long expectJob,long jobRoleId,long expectJobId,long today,Integer jobid) {
		List<JobDesc> list = BaseDAO.queryForList(JobDesc.class,SQL_GET_SIMILARPOSI2,roleId,expectJob,jobRoleId,expectJobId,today,jobid);
		return list;
	}
	// and companyId <> ?
	public List<JobDesc> searchJobsByDomain(Integer expectJob, long roleId, int companyId, String companys,
			String ids, long today, int cityId, Integer offset,int pageSize) {
		String SQL_GET_REWARD_DOMAIN_PAGE5 = "select * from (select * from tb_jobdesc "
				+ "where status > 0 " + " and (platformid="+PlatformEnum.Candidate.getValue()+" or platformid=3) "
				+ " and "+today+" < expireddate and  roleid  in ("+expectJob+" ,"+roleId+") and cityid = "+cityId+" " 
				+ " and  companyId in ( "+companys+" ) " 
				+ " union "
				+ " select * from tb_jobdesc where status > 0 " + " and (platformid="+PlatformEnum.Candidate.getValue()+" or platformid=3) "
				+ " and "+today+" < expireddate and  roleid in("+expectJob+","+roleId+") and cityid = "+cityId+" "
				+ " and domainid in ("+ids+")"
				+") jobs order by id desc limit "+offset+","+pageSize+"";
		System.out.println(SQL_GET_REWARD_DOMAIN_PAGE5);
		List<JobDesc> list = BaseDAO.queryForList(JobDesc.class, SQL_GET_REWARD_DOMAIN_PAGE5);//companyId,
		return list;
	}
	
	public List<JobDesc> searchJobsByDomainPAGE6(Integer expectJob, long roleId, int companyId, String companys,
			String ids, long today, int cityId, Integer offset,int pageSize) {
		String SQL_GET_REWARD_DOMAIN_PAGE6 = "select * from (select * from tb_jobdesc "
				+ "where status > 0 " + " and (platformid="+PlatformEnum.Candidate.getValue()+" or platformid=3) "
				+ " and "+today+" < expireddate and  roleid  in ("+expectJob+" ,"+roleId+") and cityid = "+cityId+" " 
				+ " and majorDomainId2 in ( "+ids+" ) " 
				+ " union "
				+ " select * from tb_jobdesc where status > 0 " + " and (platformid="+PlatformEnum.Candidate.getValue()+" or platformid=3) "
				+ " and "+today+" < expireddate and  roleid in("+expectJob+","+roleId+") and cityid = "+cityId+" "
				+ " and majorDomainId3 in ("+ids+")"
				+") jobs order by id desc limit "+offset+","+pageSize+"";
		System.out.println(SQL_GET_REWARD_DOMAIN_PAGE6);
		List<JobDesc> list = BaseDAO.queryForList(JobDesc.class, SQL_GET_REWARD_DOMAIN_PAGE6);//companyId,
		return list;
	}
	
	final String SQL_GET_REWARD_DOMAIN_PAGE1="select * from tb_jobdesc where status > 0 and (platformid=? or platformid=3)"
			+ " and ?<expireddate and roleid in(?,?) and companyId in(?) and cityid=? order by updateDate desc";
	public List<JobDesc> searchJobsByDomainPage1(Integer expectJob, long roleId, int companyId, String companys,
			String domainIds, long today, int cityId, Integer offset,
			int pageSize) {
		List<JobDesc> list = BaseDAO.queryForList(JobDesc.class, SQL_GET_REWARD_DOMAIN_PAGE1,
				PlatformEnum.Candidate.getValue(),today,expectJob,roleId,companys,cityId);//companyId,
		return list;
	}
	
	final String SQL_GET_REWARD_DOMAIN_PAGE2="select * from tb_jobdesc where status > 0 and (platformid=? or platformid=3)"
			+ " and ?<expireddate and roleid in(?,?) and domainid in(?) and cityid=? order by updateDate desc";
	public List<JobDesc> searchJobsByDomainPage2(Integer expectJob, long roleId, int companyId, String companys,
			String domainIds, long today, int cityId, Integer offset,
			int pageSize) {
		List<JobDesc> list = BaseDAO.queryForList(JobDesc.class, SQL_GET_REWARD_DOMAIN_PAGE2,
				PlatformEnum.Candidate.getValue(),today,expectJob,roleId,domainIds,cityId);//companyId,
		return list;
	}
	
	final String SQL_GET_REWARD_DOMAIN_PAGE3="select * from tb_jobdesc where status > 0 and (platformid=? or platformid=3)"
			+ " and ?<expireddate and roleid in(?,?) and majorDomainId2 in(?) and cityid=? order by updateDate desc";
	public List<JobDesc> searchJobsByDomainPage3(Integer expectJob, long roleId, int companyId, String companys,
			String domainIds, long today, int cityId, Integer offset,
			int pageSize) {
		List<JobDesc> list = BaseDAO.queryForList(JobDesc.class, SQL_GET_REWARD_DOMAIN_PAGE3,
				PlatformEnum.Candidate.getValue(),today,expectJob,roleId,domainIds,cityId);//companyId,
		return list;
	}
	
	final String SQL_GET_REWARD_DOMAIN_PAGE4="select * from tb_jobdesc where status > 0 and (platformid=? or platformid=3)"
			+ " and ?<expireddate and roleid in(?,?) and majorDomainId3 in(?) and cityid=? order by updateDate desc";
	public List<JobDesc> searchJobsByDomainPage4(Integer expectJob, long roleId, int companyId, String companys,
			String domainIds, long today, int cityId, Integer offset,
			int pageSize) {
		List<JobDesc> list = BaseDAO.queryForList(JobDesc.class, SQL_GET_REWARD_DOMAIN_PAGE4,
				PlatformEnum.Candidate.getValue(),today,expectJob,roleId,domainIds,cityId);//companyId,
		return list;
	}
	
	
	public int searchJobsByDomainCount(Integer expectJob, long roleId, int companyId, String companys, long today, int cityId,
			String domainIds){
			String SQL_GET_REWARD_DOMAIN_PAGE5_COUNT = "select count(*) from (select * from tb_jobdesc "
				+ "where status > 0 " + " and (platformid="+PlatformEnum.Candidate.getValue()+" or platformid=3) "
				+ " and "+today+" < expireddate and  roleid  in ("+expectJob+" ,"+roleId+") and cityid = "+cityId+" " 
				+ " and  companyId in ( "+companys+" ) " + " union "
				+ " select * from tb_jobdesc where status > 0 " 
				+ " and (platformid="+PlatformEnum.Candidate.getValue()+" or platformid=3) "
				+ " and "+today+" < expireddate and  roleid in("+expectJob+","+roleId+") and cityid ="+cityId+""
				+ " and domainid in ("+domainIds+")"
				+ " union "
				+ " select * from tb_jobdesc where status > 0 " + " and (platformid="+PlatformEnum.Candidate.getValue()+" or platformid=3) "
				+ " and "+today+" < expireddate and  roleid in("+expectJob+","+roleId+") and cityid = "+cityId+" "
				+ " and majorDomainId2 in ("+domainIds+")"
				+ " union "
				+ " select * from tb_jobdesc where status > 0 " + " and (platformid="+PlatformEnum.Candidate.getValue()+" or platformid=3) "
				+ " and "+today+" < expireddate and  roleid in("+expectJob+","+roleId+") and cityid = "+cityId+" "
				+ " and majorDomainId3 in ("+domainIds+")"
				+ ") jobs LIMIT 0,1";//and companyId <> ?  	
			long jobscount= (Long) BaseDAO.queryOneRowOneCol(SQL_GET_REWARD_DOMAIN_PAGE5_COUNT);//companyId
		 	System.out.println("search domain count="+jobscount);
		 	Integer jobcount=(int)jobscount;
		 	return jobcount;
	}
	
	final String SQL_GET_BY_ID= "SELECT job.*,d.name as 'domainName' from tb_jobdesc as job left join tb_domain as d on job.domainid = d.id where job.id=? ";
	public JobDesc getById(int jobId) {
		JobDesc job = BaseDAO.queryForObject(JobDesc.class,SQL_GET_BY_ID,jobId);
		return job;
	}
	
	final String SQL_GET_RECOMMEND_JOB= "SELECT * from tb_recommend where personid=createby and createby=? order by createdate DESC";
	public List<Recommend> getRecommendByJob(int id){
		List<Recommend> rep=BaseDAO.queryForList(Recommend.class, SQL_GET_RECOMMEND_JOB, id);
		return rep;
	}
	
	final String SQL_GET_OTHERRECOMMEND_JOB= "SELECT * from tb_recommend where personid<>createby and createby=? order by createdate DESC";
	public List<Recommend> getOtherRecommendByJob(int id){
		List<Recommend> rep=BaseDAO.queryForList(Recommend.class, SQL_GET_OTHERRECOMMEND_JOB, id);
		return rep;
	}
	
	public long getSumByCompanyId(int id) {
		String sql = "select count(1) from tb_jobdesc where status = 1 and platformid != 2 and companyid = ?";
		return BaseDAO.getCountBySql(sql, id);
	}

	public List<JobDesc> getListNoCompanyId() {
		// TODO Auto-generated method stub
		String sql = "select * from tb_jobdesc where (companyid = 0 or cityid is null)";
		return BaseDAO.queryForList(JobDesc.class, sql);
	}

	public void updateBatch(List<JobDesc> jobs) {
		// TODO Auto-generated method stub
		for (JobDesc j : jobs) {
			String sql = "update tb_jobdesc set companyid = "+j.getCompanyId()
			+",domainid = "+j.getDomainID()+",cityid = "+j.getCityId()+" where id  = "+j.getId();
			System.out.println(sql);
			BaseDAO.execute(sql);	
		}
	}

	public List<JobDesc> listJobByParam(List<Integer> companyIds, String positions, String companys, Integer dId,Integer fId, Integer city,String positionName, int offset, Integer pageSize) {
		StringBuilder sb = new StringBuilder(SQL_LIST_JOBDESC_BY_PARAM);
		if (StringUtils.isNotEmpty(positions)){
			sb.append(" and job.roleid in (").append(positions).append(")");
		}
		if (StringUtils.isNotEmpty(companys)){
			sb.append(" and job.companyid in (" ).append(companys).append(")");
		}
		if (fId != null && fId.intValue() != 0){
			sb.append("and titleGroup.id = ").append(fId);

		}

		if (dId != null && dId.intValue() != 0){
			sb.append(" and job.domainid =").append(dId);

		}
		if (city != null && city.intValue() != 0){
			sb.append(" and job.cityid =").append(city);
		}
		if (StringUtils.isNotEmpty(positionName)){
			sb.append(" and (job.name like '%").append(positionName).append("%'");
			if ( companyIds != null){
				sb.append("or job.companyid in (");
				for(Integer s : companyIds){
					sb.append(s).append(",");
				}
				sb.deleteCharAt(sb.length()-1);
				sb.append(")");
			}
			sb.append(")");
		}

		sb.append(" order by job.id desc limit ?,?");
		System.out.println("sql: " + sb.toString());
		List<JobDesc> list = BaseDAO.queryForList(JobDesc.class,sb.toString(),offset,pageSize);
		return list;
	}

	public int getJobCountByParam(List<Integer> companyIds, String positions, String companys, Integer dId,Integer fId, Integer city, String positionName) {
		StringBuilder sb = new StringBuilder(SQL_LIST_JOBDESC_BY_PARAM);
		if (StringUtils.isNotEmpty(positions)){
			sb.append(" and job.roleid in (").append(positions).append(")");
		}
		if (StringUtils.isNotEmpty(companys)){
			sb.append(" and job.companyid in (" ).append(companys).append(")");
		}
		if (fId != null && fId.intValue() != 0){
			sb.append("and titleGroup.id = ").append(fId);
		}
		if (dId != null && dId.intValue() != 0){
			sb.append(" and job.domainid =").append(dId);

		}
		if (city != null && city .intValue() != 0){
			sb.append(" and job.cityid =").append(city);
		}
		if (StringUtils.isNotEmpty(positionName)){
			sb.append(" and (job.name like '%").append(positionName).append("%'");
			if ( companyIds != null){
				sb.append("or job.companyid in (");
				for(Integer s : companyIds){
					sb.append(s).append(",");
				}
				sb.deleteCharAt(sb.length()-1);
				sb.append(")");
			}
			sb.append(")");
		}
		System.out.println("getJobCountByParam count: " + sb.toString());
		List<JobDesc> list = BaseDAO.queryForList(JobDesc.class,sb.toString());
		return list.size();
	}
}
